﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoyoEden.Models;
using System.Data.Common;
using System.Data;

namespace CoyoEden.Providers
{
	public partial class DbPortalProvider
	{
        public override CoyoEden.Models.PortalFile SelectPortalFile(int id)
		{
			var item = PortalFile.PortalFiles.Find(c=>c.Id==id);
			if (item != null) item.MarkOld();
			return item;
		}

        public override void InsertPortalFile(CoyoEden.Models.PortalFile item)
		{
			List<PortalFile> items = PortalFile.PortalFiles;
			items.Add(item);
			using(DbConnection conn=MyDbFactory.CreateConnection() ){
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using(DbCommand cmd=conn.CreateCommand()){
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("INSERT INTO {0}PortalFiles (Name,Url,Hits,Description) VALUES ({1}name,{1}url,{1}hits,{1}desc);",tablePrefix,parmPrefix);
					sql.AppendFormat("SELECT Id FROM {0}PortalFiles WHERE Url={1}url;",tablePrefix,parmPrefix);
					cmd.CommandType = CommandType.Text;
					cmd.CommandText = sql.ToString();

					DbParameter dpName = MyDbFactory.CreateParameter();
					dpName.ParameterName = parmPrefix + "name";
					dpName.Value = item.Name;
					cmd.Parameters.Add(dpName);

					DbParameter dpUrl = MyDbFactory.CreateParameter();
					dpUrl.ParameterName = parmPrefix + "url";
					dpUrl.Value = item.Url;
					cmd.Parameters.Add(dpUrl);

					DbParameter dpHits = MyDbFactory.CreateParameter();
					dpHits.ParameterName = parmPrefix + "hits";
					dpHits.Value = item.Hits;
					cmd.Parameters.Add(dpHits);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "desc";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					var itemId = cmd.ExecuteScalar();
					item.Id = Convert.ToInt32(itemId);
				}
			}
		}

        public override void UpdatePortalFile(CoyoEden.Models.PortalFile item)
		{
			List<PortalFile> items = PortalFile.PortalFiles;
			items.Remove(item);
			items.Add(item);

			using(DbConnection conn=MyDbFactory.CreateConnection()){
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using(DbCommand cmd=conn.CreateCommand()){
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("UPDATE {0}PortalFiles ",tablePrefix);
					sql.AppendFormat("SET Name={0}name,Url={0}url,Hits={0}hits,Description={0}desc WHERE Id={0}id;",parmPrefix);
					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpName = MyDbFactory.CreateParameter();
					dpName.ParameterName = parmPrefix + "name";
					dpName.Value = item.Name;
					cmd.Parameters.Add(dpName);

					DbParameter dpUrl = MyDbFactory.CreateParameter();
					dpUrl.ParameterName = parmPrefix + "url";
					dpUrl.Value = item.Url;
					cmd.Parameters.Add(dpUrl);

					DbParameter dpHits = MyDbFactory.CreateParameter();
					dpHits.ParameterName = parmPrefix + "hits";
					dpHits.Value = item.Hits;
					cmd.Parameters.Add(dpHits);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "desc";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
			}//endof using
			
		}

        public override void DeletePortalFile(CoyoEden.Models.PortalFile item)
		{
			List<PortalFile> items = PortalFile.PortalFiles;
			items.Remove(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("DELETE FROM {0}AlbumFile ", tablePrefix);
					sql.AppendFormat("WHERE FileID = {0}id;", parmPrefix);
					sql.AppendFormat("DELETE FROM {0}PortalFiles WHERE Id={1}id;", tablePrefix, parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
			}
		}

        public override List<CoyoEden.Models.PortalFile> FillPortalFiles()
		{
			List<PortalFile> items = new List<PortalFile>();

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;

				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("SELECT Id, Name, Url,Hits,Description ");
					sql.AppendFormat("FROM {0}PortalFiles ", tablePrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;
					conn.Open();

					using (DbDataReader rdr = cmd.ExecuteReader())
					{
						if (rdr.HasRows)
						{
							while (rdr.Read())
							{
								PortalFile item = new PortalFile();
								item.Name = rdr.GetString(1);
								item.Url = rdr.GetString(2);
								item.Hits = rdr.GetInt32(3);
								item.Description = rdr.GetString(4);
								item.Id = rdr.GetInt32(0);
								items.Add(item);
								item.MarkOld();
							}
						}
					}
				}
			}

			return items;
		}
	}
}
